package com.pmcc.core.repository;


import com.pmcc.core.entity.City;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * Created by spurs on 2018/3/26.
 */
public interface CityRepository extends JpaRepository<City,String> {
    //查询国家列表
    @Modifying
    @Query(value ="select * from SYS_CITY where id like '%000000' order by id",  nativeQuery = true)
    List<City> queryCountryCombo();
    //查询国家内的省份
    @Modifying
    @Query(value ="select * from SYS_CITY where id like CONCAT(SUBSTRING(:id,1,3),'%') \n" +
            "and id like CONCAT('%',SUBSTRING(:id,6,9)) and id!= :id order by id",  nativeQuery = true)
    List<City> queryProvinceCombo(@Param(value = "id") String id);
    //查询省下面的市
    @Modifying
    @Query(value ="select * from SYS_CITY where id like CONCAT(SUBSTRING(:id,1,5),'%') \n" +
            "and id!= :id and id like '%00' order by id ",  nativeQuery = true)
    List<City> queryCityCombo(@Param(value = "id") String id);
    //查询市下面的区
    @Modifying
    @Query(value ="select * from SYS_CITY where id like CONCAT(SUBSTRING(:id,1,7),'%')  and id!= :id order by id",  nativeQuery = true)
    List<City> queryAreaCombo(@Param(value = "id") String id);

    City findByName(String name);
}
